from PySide6.QtGui import *
from PySide6.QtWidgets import *
from PySide6.QtCore import *
from common_code import *
import re
import win32com.client as win32
import pythoncom
import xlwings as xw

# 定义第一个页面
class Page_DRBFM(QWidget):
    def __init__(self):
        super().__init__()
        self.label = QLabel("HMI DRBFM 文字自适应设置工具")
        self.label_1 = QLabel("注意 : 运行工具时，Excel 进程可能会被强制终止，请提前保存本地 Excel 文件，以免数据丢失。")
        self.label_1.setStyleSheet("color: red;")
        self.label.setAlignment(Qt.AlignCenter)  # 方法1：直接设置对齐方式
        self.DRBFM_path = OpenFileWidget('DRBFM path:    ')
        self.Rules_path = OpenFileWidget('Rules path:    ')
        self.start_btn = StartButtonWidget(self.start_exce)
        layout = QVBoxLayout()
        layout.addWidget(self.label)
        layout.addWidget(self.DRBFM_path)
        layout.addWidget(self.Rules_path)
        layout.addWidget(self.start_btn)
        layout.addWidget(self.label_1)
        self.setLayout(layout)

    def clear(self):
        # 清除UI中的输入和显示
        self.DRBFM_path.clear()
        self.Rules_path.clear()
        self.start_btn.clear()
    
    def start_exce(self):
        if not confirm_file_status(self):
            return
        Rules_path = self.Rules_path.edit.text().strip()
        if not Rules_path or not os.path.isfile(Rules_path):
            QMessageBox.critical(self, "错误", f"[Rules path]为空或不是文件")
            return
        DRBFM_path = self.DRBFM_path.edit.text().strip()
        if not DRBFM_path or not os.path.isfile(DRBFM_path):
            QMessageBox.critical(self, "错误", f"[DRBFM path]为空或不是文件")
            return
        try:
            Rules_reader = ExcelReader(Rules_path)
        except Exception as e:
            QMessageBox.critical(self, "错误", f"读取Rules文件失败: {e}")
            return
            
        try:
            excel = win32.Dispatch('Excel.Application')
            excel.Visible = False  # 设置为True以查看Excel窗口
            # excel.ScreenUpdating = False  # 开始前  禁用屏幕刷新提升速度
            # 打开工作簿
            workbook = excel.Workbooks.Open(DRBFM_path)
            # 获取指定的工作表
            sheet_tmp = workbook.Sheets("１．変更一覧 ")
            # 读取指定单元格的值
            cell_value = sheet_tmp.Range("C7").Value
            cell_list = cell_value.split('\n', 1)
            print (cell_list)
            if ":" in cell_list[0]:
                part1, part2 = cell_list[0].split(':', 1)
            elif "：" in cell_list[0]:
                part1, part2 = cell_list[0].split('：', 1)
            else:
                QMessageBox.critical(self, "错误", f"文件[{DRBFM_path}]获取模块名失败，请检查[１．変更一覧 ][C7]单元格")
                return
            rules_sheet = part2.strip()
            print (f"当前模块[{rules_sheet}]")
            # 读取整个sheet的数据
            sheet_list = []
            sheet_data = []
            check_sheet=Rules_reader.get_sheet(rules_sheet)
            if not check_sheet:
                QMessageBox.critical(self, "错误", f"[{Rules_path}]文件中未找到模块[{rules_sheet}]规则数据")
                return
            else:
                sheet_info = Rules_reader.read_sheet_data(rules_sheet)
                print (f"[{rules_sheet}] 所有例外规则 [{sheet_info}]")
                for row_list in sheet_info[1:]:
                    if str(row_list[1]).strip() == "" or str(row_list[2]).strip() == "":
                        QMessageBox.critical(self, "错误", f"[{Rules_path}]文件中有空数据 !!!")
                        return
                    if str(row_list[1]).strip() == "-" and str(row_list[2]).strip() == "-":
                        sheet_list.append(str(row_list[0]).strip())
                        continue
                    sheet_data.append(row_list)
                print (f"[{rules_sheet}] 例外sheet [{sheet_list}]")
                print (f"[{rules_sheet}] 例外检查规则 [{sheet_data}]")

            # 获取所有工作表
            sheets = workbook.Worksheets
            sheets_count = workbook.Worksheets.Count
            sheet_num = 1
            # 遍历每个工作表
            for sheet in sheets:
                if sheet.Name in sheet_list:
                    print(f"sheet [{sheet.Name}] 不检查, 跳过处理")
                    continue
                # 遍历所有形状（包括文本框和矩形）
                for shape in sheet.Shapes:
                    if shape.Type in (17, 1) and shape.Visible == 1:
                        try:
                            # 更安全的文本获取方式
                            if hasattr(shape, 'TextFrame2'):
                                text = shape.TextFrame2.TextRange.Text
                                if not text or text.isspace():
                                    continue
                        except AttributeError:
                            print(f"警告: [{sheet.Name}]图形 {shape.Name} 的 TextFrame2 不支持 TextRange, 跳过处理")
                            continue
                        except pythoncom.com_error as e:
                            if e.excepinfo[5] == -2147024809:  # 边界错误代码
                                print(f"警告: [{sheet.Name}]图形 {shape.Name} 是连接器/线条，跳过处理")
                            else:
                                print(f"COM 警告: {e}, 警告[{sheet.Name}]图形 {shape.Name} 需要修复状态, 跳过处理")
                            continue
                        
                        setting_flag = True
                        rgb_list = ""
                        for row_list in sheet_data:
                            if str(row_list[0]).strip() == str(sheet.Name).strip():
                                if str(row_list[1]).strip() == str(text).strip():
                                    rgb_value = str(row_list[2]).strip()
                                    setting_flag = False
                                    print(f"Processing shape: [{shape.Name}], Text: [{text}], rules text: [{str(row_list[1]).strip()}]")
                                    break

                        if setting_flag == True:
                            shape.TextFrame2.AutoSize = 1
                            continue
                        else:
                            if rgb_value == "-":
                                continue
                            else:
                                rgb_list = rgb_value.split("/")
                                rgb_color = (int(rgb_list[2]) << 16) | (int(rgb_list[1]) << 8) | int(rgb_list[0])
                        
                        try:
                            if hasattr(shape, "Fill"):
                                if shape.Fill.Visible == -1:  # 填充可见
                                    fill_color = shape.Fill.ForeColor.RGB
                                    if rgb_color == fill_color:
                                        print(f"Processing shape: [{shape.Name}], Text: [{text}], rules text: [{str(row_list[1]).strip()}, Fill Color: [{fill_color}], rgb Color: [{rgb_color}]")
                                        continue

                            shape.TextFrame2.AutoSize = 1
                        except Exception as e:
                            print(f"处理[{sheet.Name}]图形 '{shape.Name}' 填充属性时出错: {e}, 跳过处理")
                self.start_btn.ProgressBar(int((sheet_num / sheets_count) * 100))
                sheet_num = sheet_num + 1

            # 保存工作簿
            workbook.Save()
        except Exception as e:
            QMessageBox.critical(self, "错误", f"处理DRBFM文件失败: {e}")
            return
            
        finally:
            # 确保资源被释放
            try:
                if workbook is not None:
                    workbook.Close(False)  # 不保存更改
            except Exception as e:
                print(f"关闭工作簿时出错: {e}")
            
            try:
                if excel is not None:
                    excel.Quit()
            except Exception as e:
                print(f"退出Excel时出错: {e}")
            # excel.ScreenUpdating = True   # 结束后
            # 强制释放COM对象
            workbook = None
            excel = None

        self.start_btn.ProgressBar(100)
        QMessageBox.information(self, "完成", f"文件[{DRBFM_path}]处理完成")


# 定义第二个页面
class Page_SJZB(QWidget):
    def __init__(self):
        super().__init__()
        self.label = QLabel("Excel 图形/文本框文字自适应设置工具")
        self.label_1 = QLabel("注意 : 运行工具时，Excel 进程可能会被强制终止，请提前保存本地 Excel 文件，以免数据丢失。")
        self.label_1.setStyleSheet("color: red;")
        self.label.setAlignment(Qt.AlignCenter)  # 方法1：直接设置对齐方式
        self.DRBFM_path = OpenFileWidget('Excel path:    ')
        self.Rules_path = OpenFileWidget('Rules path:    ')
        self.Rules_sheet = LineTextWidget(title = 'Rules sheet:    ', max_length = 20, display_width = 200, text = "最大长度20个字符")
        self.start_btn = StartButtonWidget(self.start_exce)
        self.table_window = TableWindow(self)
        layout = QVBoxLayout()
        layout.addWidget(self.label)
        layout.addWidget(self.DRBFM_path)
        layout.addWidget(self.Rules_path)
        layout.addWidget(self.Rules_sheet)
        layout.addWidget(self.start_btn)
        layout.addWidget(self.label_1)
        # 添加分隔线
        separator = QFrame()
        separator.setFrameShape(QFrame.HLine)
        separator.setFrameShadow(QFrame.Sunken)
        layout.addWidget(separator)
        layout.addWidget(self.table_window)
        self.setLayout(layout)

    def clear(self):
        # 清除UI中的输入和显示
        self.DRBFM_path.clear()
        self.Rules_path.clear()
        self.Rules_sheet.clear()
        self.start_btn.clear()
        self.table_window.clear_table()
    
    def start_exce(self):
        if not confirm_file_status(self):
            return
        sample_data = []
        rules_sheet = self.Rules_sheet.line_text.text().strip()
        if not rules_sheet:
            QMessageBox.critical(self, "错误", f"[Rules sheet]为空, 请输入要检查的例外规则sheet")
            return
        DRBFM_path = self.DRBFM_path.edit.text().strip()
        if not DRBFM_path or not os.path.isfile(DRBFM_path):
            QMessageBox.critical(self, "错误", f"[{DRBFM_path}]为空或不是文件")
            return
        Rules_path = self.Rules_path.edit.text().strip()
        if not Rules_path or not os.path.isfile(Rules_path):
            QMessageBox.critical(self, "错误", f"[{Rules_path}]为空或不是文件")
            return
        # 读取整个sheet的数据
        sheet_list = []
        sheet_data = []
        try:
            Rules_reader = ExcelReader(Rules_path)
            check_sheet=Rules_reader.get_sheet(rules_sheet)
            if not check_sheet:
                QMessageBox.critical(self, "错误", f"[{Rules_path}]文件中未找到模块[{rules_sheet}]例外规则sheet")
                return
            else:
                sheet_info = Rules_reader.read_sheet_data(rules_sheet)
                print (f"[{rules_sheet}] 所有例外规则 [{sheet_info}]")
                for row_list in sheet_info[1:]:
                    if str(row_list[1]).strip() == "" or str(row_list[2]).strip() == "":
                        QMessageBox.critical(self, "错误", f"[{Rules_path}]文件中有空数据 !!!")
                        return
                    if str(row_list[1]).strip() == "-" and str(row_list[2]).strip() == "-":
                        sheet_list.append(str(row_list[0]).strip())
                        continue
                    sheet_data.append(row_list)
                print (f"[{rules_sheet}] 例外sheet [{sheet_list}]")
                print (f"[{rules_sheet}] 例外检查规则 [{sheet_data}]")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"读取Rules文件失败: {e}")
            return
        if len(sheet_list) == 0 and len(sheet_data) == 0:
            sample_data.append([f"{Rules_path}文件", {rules_sheet}, "-", "-", "无例外规则数据, 按默认规则设置 !!!"])

        try:
            excel = win32.Dispatch('Excel.Application')
            excel.Visible = False  # 设置为True以查看Excel窗口
            # excel.ScreenUpdating = False  # 开始前  禁用屏幕刷新提升速度
            # 打开工作簿
            workbook = excel.Workbooks.Open(DRBFM_path)
            # 获取所有工作表
            sheets = workbook.Worksheets
            sheets_count = workbook.Worksheets.Count
            sheet_num = 1
            # 遍历每个工作表
            for sheet in sheets:
                if sheet.Name in sheet_list:
                    sample_data.append([DRBFM_path, sheet.Name, "-", "-", "不检查, 跳过处理"])
                    continue
                    
                sheet.Activate()  # 确保目标 Sheet 是活动状态
                # time.sleep(0.5)
                excel.ActiveWindow.Zoom = 100  # 设置当前活动窗口的缩放比例,强制100%显示
                # 强制计算或等待一下，确保缩放生效
                excel.Calculate()
                # 遍历所有形状（包括文本框和矩形）
                for shape in sheet.Shapes:
                    top_left_cell = shape.TopLeftCell
                    bottom_right_cell = shape.BottomRightCell
                    # 检查行是否隐藏
                    rows_hidden = any(sheet.Rows(row).Hidden 
                                for row in range(top_left_cell.Row, bottom_right_cell.Row + 1))
                    # 检查列是否隐藏
                    cols_hidden = any(sheet.Columns(col).Hidden 
                                for col in range(top_left_cell.Column, bottom_right_cell.Column + 1))
                    if not rows_hidden and not cols_hidden and shape.Visible == -1:
                        if shape.Type == 6:  # 6表示组合图形(msoGroup)
                            print(f"发现组合图形: {shape.Name}")
                            process_group_shape(shape, sheet, DRBFM_path, sheet_data, sample_data)
                        else:
                            # 处理普通图形
                            process_single_shape(shape, sheet, DRBFM_path, sheet_data, sample_data)
                self.start_btn.ProgressBar(int((sheet_num / sheets_count) * 100))
                sheet_num = sheet_num + 1

            # 保存工作簿
            workbook.Save()
        except Exception as e:
            print(e)
            QMessageBox.critical(self, "错误", f"处理DRBFM文件失败: {e}")
            return
            
        finally:
            # 确保资源被释放
            try:
                if workbook is not None:
                    workbook.Close(False)  # 不保存更改
            except Exception as e:
                print(f"关闭工作簿时出错: {e}")
            
            try:
                if excel is not None:
                    excel.Quit()
            except Exception as e:
                print(f"退出Excel时出错: {e}")
            # excel.ScreenUpdating = True   # 结束后
            # 强制释放COM对象
            workbook = None
            excel = None

        self.start_btn.ProgressBar(100)
        if sample_data:
            self.table_window.set_data(sample_data)
            self.table_window.set_header(["file name","sheet", "Address", "text+color", "Description"])
            self.table_window.show_table()
            QMessageBox.information(self, "完成", f"[{DRBFM_path}]文件根据文本调整图形大小设置完成, 请注意查看具体处理日志信息 !!!")
        else:
            QMessageBox.information(self, "完成", f"[{DRBFM_path}]文件根据文本调整图形大小设置完成 !!!")
 
# 定义第三个页面
class Page_PD(QWidget):
    def __init__(self):
        super().__init__()
        self.label = QLabel("PD文档新版本号标注以及旧版本内容颜色变更工具")
        self.label.setAlignment(Qt.AlignCenter)  # 方法1：直接设置对齐方式
        self.pd_path = OpenFileWidget('PD path:    ')
        layout_1 = QHBoxLayout()
        self.target_ver = LineTextWidget(title = 'target version:    ', max_length = 10, display_width = 200, text = "最大长度10个字符")
        self.change_his = LineTextWidget(title = 'change history:    ', max_length = 20, display_width = 200, text = "最大长度20个字符")
        layout_1.addWidget(self.target_ver)
        layout_1.addWidget(self.change_his)
        self.start_btn1 = StartButtonWidget(self.start_exce1, "Old version annotation")
        self.start_btn2 = StartButtonWidget(self.start_exce2, "New version annotation")
        self.table_window = TableWindow(self)
        layout = QVBoxLayout()
        layout.addWidget(self.label)
        layout.addWidget(self.pd_path)
        layout.addLayout(layout_1)
        layout.addWidget(self.start_btn1)
        layout.addWidget(self.start_btn2)
        self.label_1 = QLabel("注意 : 运行工具时，Excel 进程可能会被强制终止，请提前保存本地 Excel 文件，以免数据丢失。")
        self.label_1.setStyleSheet("color: red;")
        layout.addWidget(self.label_1)
        # 添加分隔线
        separator = QFrame()
        separator.setFrameShape(QFrame.HLine)
        separator.setFrameShadow(QFrame.Sunken)
        layout.addWidget(separator)
        layout.addWidget(self.table_window)
        self.setLayout(layout)

    def clear(self):
        # 清除UI中的输入和显示
        self.pd_path.clear()
        self.target_ver.clear()
        self.change_his.clear()
        self.start_btn1.clear()
        self.start_btn2.clear()
        self.table_window.clear_table()
    
    def start_exce1(self):
        self.table_window.clear_table()
        self.start_btn1.ProgressBar(0)
        if not confirm_file_status(self):
            return
        pd_path = self.pd_path.edit.text().strip()
        if not pd_path or not os.path.isfile(pd_path):
            QMessageBox.critical(self, "错误", f"[{pd_path}]为空或不是文件")
            return
        target_ver = self.target_ver.line_text.text().strip()
        if not target_ver:
            QMessageBox.critical(self, "错误", f"[target version]为空, 请输入")
            return
        change_his = self.change_his.line_text.text().strip()
        if not change_his:
            QMessageBox.critical(self, "错误", f"[change history]为空, 请输入")
            return
        
        sample_data = []
        with ExcelHandler() as excel:
            if excel.open_workbook(pd_path):
                all_sheet = excel.get_all_sheet_names()
                sheets_count = len(all_sheet)
                sheet_num = 1
                for sheet_name in all_sheet:
                    row_list = []
                    sheet = excel.get_sheet(sheet_name)
                    sheet.Activate()  # 确保目标 Sheet 是活动状态
                    # time.sleep(0.5)
                    # # 强制Excel应用程序重绘屏幕
                    # excel.excel_app.ScreenUpdating = False  # 先关闭屏幕更新
                    # excel.excel_app.ScreenUpdating = True   # 再开启屏幕更新，这会触发重绘
                    excel.excel_app.ActiveWindow.Zoom = 100  # 设置当前活动窗口的缩放比例,强制100%显示
                    sheet.Tab.Color = False  # False/-1 标签变为透明/无色
                    sample_data.append([pd_path, sheet_name, "-", "-","设置sheet页标签为透明/无色"])
                    if sheet_name == change_his:
                        last_row = sheet.Cells(sheet.Rows.Count, 3).End(win32.constants.xlUp).Row
                        # 获取Range对象
                        col_data = sheet.Range(f"C1:C{last_row}").Value
                        for row,data in enumerate(col_data, start=1):
                            if str(data[0]).strip() == target_ver:
                                sheet.Rows(row).Font.Color = 0
                                sample_data.append([pd_path, sheet_name, row, "-","行标注为黑色"])
                                row_list.append(row)
                    else:
                        last_row = sheet.Cells.Find("*", SearchOrder=win32.constants.xlByRows, SearchDirection=win32.constants.xlPrevious).Row
                        print(f"sheet_name = {sheet_name} last_row={last_row}")
                        for row in range(1, last_row + 1):
                            last_cell = sheet.Cells(row, sheet.Columns.Count).End(win32.constants.xlToLeft)
                            if str(last_cell.Value).strip() == f"▲{target_ver}":
                                # print((f"A{row}:{get_column_letter(last_cell.Column)}{row}"))
                                # sheet.Range(f"A{row}:{get_column_letter(last_cell.Column)}{row}").Font.Color = 0
                                range_cell = sheet.Range(f"A{row}:{get_column_letter(last_cell.Column)}{row}")
                                for cell in range_cell:
                                    if cell.Font.Color == 255:
                                        cell.Font.Color = 0
                                    else:
                                        try:
                                            # ✅ 检查字符级别的红色字体,确保 GetCharacters 可用
                                            if hasattr(cell, "GetCharacters"):
                                                for i in range(1, len(str(cell.Value)) + 1):
                                                    if cell.GetCharacters(Start=i, Length=1).Font.Color == 255:
                                                        cell.Font.Color = 0
                                                        break
                                        except Exception as e:
                                            sample_data.append([pd_path, sheet_name, str(cell.Address).replace("$",""), "-",f"检查字符字体颜色失败{e}"])
                                            print(f"{str(cell.Address).replace("$","")}检查字符字体颜色失败{e}")
                                            continue
                                    # 检查单元格是否有超链接
                                    try:
                                        # 尝试获取第一个超链接
                                        hlink = cell.Hyperlinks(1)
                                        # 如果有超链接且未设置过样式，则设置蓝色和下划线
                                        if hlink:
                                            print(f"检查到超链接{cell.Address}")
                                            cell.Font.ColorIndex = 5
                                            sample_data.append([pd_path, sheet_name, row, cell.Address,"超链接标注为蓝色"])
                                    except:
                                        pass
                                # sheet.Rows(row).Font.Color = 0
                                sample_data.append([pd_path, sheet_name, row, "-","行标注为黑色"])
                                row_list.append(row)

                    # 遍历所有形状（包括文本框和矩形）
                    for shape in sheet.Shapes:
                        top_left_cell = shape.TopLeftCell
                        bottom_right_cell = shape.BottomRightCell
                        # 检查行是否隐藏
                        rows_hidden = any(sheet.Rows(row).Hidden 
                                    for row in range(top_left_cell.Row, bottom_right_cell.Row + 1))
                        # 检查列是否隐藏
                        cols_hidden = any(sheet.Columns(col).Hidden 
                                    for col in range(top_left_cell.Column, bottom_right_cell.Column + 1))
                        if not rows_hidden and not cols_hidden and shape.Visible == -1:
                            if shape.Type == 6:  # 6表示组合图形(msoGroup)
                                # print(f"发现组合图形: {shape.Name}")
                                setting_group_shape(shape, sheet, pd_path, row_list, sample_data)
                            else:
                                # 处理普通图形
                                setting_single_shape(shape, sheet, pd_path, row_list, sample_data)

                    self.start_btn1.ProgressBar(int((sheet_num / sheets_count) * 100))
                    sheet_num = sheet_num + 1
            excel.save()
            
        self.start_btn1.ProgressBar(100)
        if sample_data:
            self.table_window.set_data(sample_data)
            self.table_window.set_header(["file name","sheet","row","cell value", "Description"])
            self.table_window.show_table()
            QMessageBox.information(self, "完成", f"[{pd_path}]文件旧版本标注已完成，请查看操作日志 !!!")
        else:
            QMessageBox.information(self, "完成", f"[{pd_path}]文件旧版本标注已完成 !!!")
    
    def start_exce2(self):
        self.table_window.clear_table()
        self.start_btn1.ProgressBar(0)
        if not confirm_file_status(self):
            return
        pd_path = self.pd_path.edit.text().strip()
        if not pd_path or not os.path.isfile(pd_path):
            QMessageBox.critical(self, "错误", f"[{pd_path}]为空或不是文件")
            return
        target_ver = self.target_ver.line_text.text().strip()
        if not target_ver:
            QMessageBox.critical(self, "错误", f"[target version]为空, 请输入")
            return
        change_his = self.change_his.line_text.text().strip()
        if not change_his:
            QMessageBox.critical(self, "错误", f"[change history]为空, 请输入")
            return

        target_value = f"▲{target_ver}"
        sample_data = []
        change_sheet = []
        change_sheet_out = ["1.変更sheet:","2.新規sheet:",""]
        with ExcelHandler() as excel:
            if excel.open_workbook(pd_path):
                if excel.sheet_exists(change_his):
                    sheet = excel.get_sheet(change_his)
                    # sheet.Activate()  # 确保目标 Sheet 是活动状态
                    # excel.excel_app.ActiveWindow.Zoom = 100  # 设置当前活动窗口的缩放比例,强制100%显示
                    # 遍历change_his工作表中的红色单元格
                    last_row, last_col = get_true_last_row_and_col(sheet)
                    print(f"sheet = {change_his}, row = {last_row}, col = {last_col}")
                    for row in range(last_row + 1, 1, -1):
                        if str(sheet.Cells(row, 3).Value).strip() == target_ver:
                            sheet.Rows(row).Font.Color = 0x0000FF  # 设置红色字体
                            sample_data.append([pd_path, change_his, row, "-","行已标注为红色"])
                            sheet_data = str(sheet.Cells(row, 5).Value).strip().split("\n")
                            for sheet_name in sheet_data:
                                sheet_str = sheet_name.strip()
                                if sheet_str and sheet_str not in change_sheet:
                                    change_sheet.append(sheet_str)
                            break
                else:
                    QMessageBox.critical(self, "错误", f"未找到[{change_his}]sheet页, 请重新输入")
                    return

                print(change_sheet)
                
                all_sheet = excel.get_all_sheet_names()
                sheets_count = len(change_sheet)
                sheet_num = 1
                for sheet_name in all_sheet:
                    target_row = []
                    sheet = excel.get_sheet(sheet_name)
                    if sheet_name in change_sheet:
                        if sheet.Tab.Color != 0x0000FF:
                            sheet.Tab.Color = 0x0000FF
                            sample_data.append([pd_path, sheet_name, "-", "-","sheet页标签颜色未标红, 已标注为红色"])
                    else:
                        if sheet.Tab.Color != False and sheet_name != change_his:
                            sample_data.append([pd_path, sheet_name, "-", "-","sheet页标签颜色不正确"])
                        continue

                    last_row, last_col = get_true_last_row_and_col(sheet)
                    print(f"sheet = {sheet_name}, row = {last_row}, col = {get_column_letter(last_col)}")
                    # 获取Range对象
                    col_data = sheet.Range(f"{get_column_letter(last_col)}1:{get_column_letter(last_col)}{last_row}").Value
                    for data in col_data:
                        if str(data[0]).strip().startswith('▲'):
                            target_col = last_col + 1
                            break
                        else:
                            target_col = last_col + 2
                    print(f"data={data[0]} target_col = {get_column_letter(target_col)}")
                    
                    # 遍历所有单元格
                    for row in range(1, last_row + 1):
                        if sheet.Rows(row).Hidden:  # 跳过隐藏行
                            continue
                        row_last_cell = sheet.Cells(row, last_col+1).End(win32.constants.xlToLeft)
                        if row_last_cell.Column == 1 and row_last_cell.Value is None:
                            # print(f"sheet = {sheet_name}, row = {row} 空行跳过")
                            continue

                        for col in range(1, last_col + 1):
                            if sheet.Columns(col).Hidden:  # 跳过隐藏列
                                continue
                            cell = sheet.Cells(row, col)
                            if cell.Value:
                                # 检查整个单元格的字体颜色
                                if cell.Font.Color == 0x0000FF:  # 红色
                                    target_row.append(row)
                                    break
                                else:
                                    try:
                                        # ✅ 检查字符级别的红色字体,确保 GetCharacters 可用
                                        if hasattr(cell, "GetCharacters"):
                                            for i in range(1, len(str(cell.Value)) + 1):
                                                if cell.GetCharacters(Start=i, Length=1).Font.Color == 0x0000FF:
                                                    target_row.append(row)
                                                    break
                                    except Exception as e:
                                        sample_data.append([pd_path, sheet_name, str(cell.Address).replace("$",""), "-",f"检查字符字体颜色失败{e}"])
                                        print(f"{str(cell.Address).replace("$","")}检查字符字体颜色失败{e}")

                    for shape in sheet.Shapes:
                        top_left_cell = shape.TopLeftCell
                        bottom_right_cell = shape.BottomRightCell
                        # 检查行是否隐藏
                        rows_hidden = any(sheet.Rows(row).Hidden 
                                    for row in range(top_left_cell.Row, bottom_right_cell.Row + 1))
                        # 检查列是否隐藏
                        cols_hidden = any(sheet.Columns(col).Hidden 
                                    for col in range(top_left_cell.Column, bottom_right_cell.Column + 1))
                        if not rows_hidden and not cols_hidden and shape.Visible == -1:
                            if shape.Type == 6:  # 6表示组合图形(msoGroup)
                                # print(f"发现组合图形: {shape.Name}")
                                check_and_set_group_shape(shape, sheet, pd_path, target_row, sample_data)
                            else:
                                # 处理普通图形
                                check_and_set_single_shape(shape, sheet, pd_path, target_row, sample_data)

                    row_list = list(set(target_row))
                    for row in row_list:
                        sheet.Cells(row, target_col).Value = target_value
                        sheet.Cells(row, target_col).Font.Color = 0x0000FF
                        sample_data.append([pd_path, sheet_name, row, "-",f"追加对应版本号[{target_value}]"])

                    self.start_btn2.ProgressBar(int((sheet_num / sheets_count) * 100))
                    sheet_num = sheet_num + 1
            excel.save()

        self.start_btn2.ProgressBar(100)
        if sample_data:
            self.table_window.set_data(sample_data)
            self.table_window.set_header(["file name","sheet","row","cell value", "Description"])
            self.table_window.show_table()
            QMessageBox.information(self, "完成", f"[{pd_path}]文件新版本标注已完成，请查看操作日志 !!!")
        else:
            QMessageBox.information(self, "完成", f"[{pd_path}]文件新版本标注已完成 !!!")
